package database.dbRelations;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import objects.Event;
import objects.Task;

import main.Main;
import database.dbObjects.IDBObject;
import database.dbObjects.IDBEvent.DBEvent;
import database.dbObjects.IDBTask.DBTask;

public interface IDBRelTaskEvent extends IDBObject {
	
	public class DBRelTaskEvent extends DBObject {
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table RelTaskEvent( " +
	        			   "Task_Name varchar(" + DBTask.maxLengthName + ") not null, " +
	        			   "Event_Start char(" + DBEvent.lengthDateTimeFormat + ") not null, " +
	        			   "Event_Summary varchar(" + DBEvent.maxLengthSummary + ") not null, " +
	        			   "foreign key (Task_Name) references Task (Name), " +
	        			   "foreign key (Event_Start) references Event (Start), " +
	        			   "foreign key (Event_Summary) references Event (Summary), " +
	        			   "primary key (Task_Name, Event_Start, Event_Summary) " +
	        			       "on conflict abort);";
			
			executeCreation(query);
		}
		
		// TODO eventual triggers
		
		public final boolean add(String taskName, String eventStart, 
				String eventSummary) throws Exception {
			
			checkConsistance(taskName, eventStart, eventSummary);
			
			Vector<Object> v = new Vector<Object>(3);
			v.add(0, taskName);
			v.add(1, eventStart);
			v.add(2, eventSummary);
			
			return super.add("RelTaskEvent", v);
		}
		
		public final boolean delete(String taskName, String eventStart, 
                String eventSummary) throws Exception {
        
	        checkConsistance(taskName, eventStart, eventSummary);
	        
	        Vector<Object> keys = new Vector<Object>(3);
	        keys.add(0, taskName);
	        keys.add(1, eventStart);
	        keys.add(2, eventSummary);
	        
	        return super.delete("RelTaskEvent", keyColumns, keys);
		}
		
	/*	public void printRelTaskEvent(String sql) {
			// TODO nog aan te passen!!
			try {
				Statement stat = Main.connection.createStatement();
				ResultSet rs = stat.executeQuery(sql);
				
				System.out.println("Vak ID\t\t|\tFile naam");
				System.out.println("---------------------------------------------------------------");
				while (rs.next()) {
					System.out.println(rs.getString("Course_ID")+"\t\t|\t"+rs.getString("File_Path"));
				}
				rs.close();
			} catch(Exception e){
				System.out.println("test-Exception P10!!!");
			}	
		}
	*/	
		
		public final Vector<Task> getTasks(String eventStart, String eventSummary) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelTaskEvent join Task " +
											 "where ((Event_Start = '" + eventStart + "') and " +
											 "(Event_Summary = '" + eventSummary + "') and " +
											 "(RelTaskEvent.Task_Name = Task.Name));");
			
			Vector<Task> tasks = new Vector<Task>();
			int counter = 0;
			while (rs.next()) {
				tasks.add(counter, new Task(rs.getString("Name"), 
						rs.getString("Description"), rs.getBoolean("Done"), 
						rs.getInt("Grades"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return tasks;
		}
		
		public final Vector<Event> getEvents(String taskName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelTaskEvent join Event " + 
					"where ((Task_Name = '" + taskName + "') and " + 
					"(RelTaskEvent.Event_Start = Event.Start) and " +
					"(RelTaskEvent.Event_Summary = Event.Summary));");
			
			Vector<Event> events = new Vector<Event>();
			int counter = 0;
			while (rs.next()) {
				events.add(counter, new Event(rs.getString("Start"), 
						rs.getString("End"), rs.getString("Summary"), 
						rs.getBoolean("IsDeadline"), rs.getString("WarningDate"), 
						rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return events;
		}
		
/**getEvents geeft normaal gezien altijd deadlines terug */
/*		public final Vector<Event> getDeadlines(String taskName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelTaskEvent join Event " + 
					"where ((Task_Name = '" + taskName + "') and " +
					"(Event.IsDeadline = 1) and " +
					"(RelTaskEvent.Event_Start = Event.Start) and " +
					"(RelTaskEvent.Event_Summary = Event.Summary));");
			
			Vector<Event> events = new Vector<Event>();
			int counter = 0;
			while (rs.next()) {
				events.add(counter, new Event(rs.getString("Start"), 
						rs.getString("End"), rs.getString("Summary"), 
						rs.getBoolean("IsDeadline"), rs.getString("WarningDate"), 
						rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return events;
		}
*/		
		private final void checkConsistance(String taskName, String eventStart, 
				String eventSummary) throws Exception {
			
			task.checkName(taskName);
			event.checkKeys(eventStart, eventSummary);
		}
		
		
		private static final Vector<String> keyColumns = new Vector<String>(3);
		static {
			keyColumns.add(0, "Task_Name");
			keyColumns.add(1, "Event_Start");
			keyColumns.add(1, "Event_Summary");
		}
		private final DBTask task = new DBTask();
		private final DBEvent event = new DBEvent();
	
	}
	
}
